package com.estun.backroundManagementService.modules.product.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.estun.backroundManagementService.modules.product.dto.ProductStatDTO;
import com.estun.backroundManagementService.modules.product.model.Product;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 产品表 Mapper 接口
 * </p>
 *
 * @author machine
 * @since 2023-02-14
 */
@Mapper
public interface ProductMapper extends BaseMapper<Product> {

    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d %H') as formattedCreateTime, status, count(1) as count\n" +
            "FROM product\n" +
            "AND type=1"+
            "WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = #{date} \n" +
            "group by DATE_FORMAT(create_time, '%Y-%m-%d %H'), status")
    List<ProductStatDTO> statNumByStatusOfPerHour(@Param("date") String date);


//    @Select("SELECT DATE_FORMAT(create_time, #{format}) as formattedCreateTime, status,station_id as stationId, count(1) as count FROM product  WHERE 1 = 1")
//    @("WHERE station_id =#{stationId} DATE_FORMAT(create_time, #{format}) between #{begin} and #{end} \n" +
//            "group by DATE_FORMAT(create_time, #{format}),status,stationId")
//    List<ProductStatDTO> statNumByDay(@Param("format") String format, @Param("stationId") String stationId,
//                                      @Param("begin") String begin, @Param("end") String end);

    @Select({"<script>",
            "SELECT DATE_FORMAT(create_time, #{format}) as formattedCreateTime, status,station_id as stationId, count(1) as count FROM product WHERE 1 = 1",
            "<when test='stationId!=null &amp; stationId!=\"\"'>",
            "AND station_id = #{stationId}",
            "</when>",
            "AND type=1",
            "AND DATE_FORMAT(create_time, #{format}) between #{begin} and #{end}",
            "group by DATE_FORMAT(create_time, #{format}),status,stationId",
            "</script>"})
    List<ProductStatDTO> statNumByDay(@Param("stationId") String stationId, @Param("format") String format,
                                      @Param("begin") String begin, @Param("end") String end);

    @Select({"<script>",
            "SELECT DATE_FORMAT(create_time, #{format}) as formattedCreateTime, count(1) as count FROM product WHERE 1 = 1",
            "<when test='stationId!=null &amp; stationId!=\"\"'>",
            "AND station_id = #{stationId}",
            "</when>",
            "AND type=1",
            "AND DATE_FORMAT(create_time, #{format}) between #{begin} and #{end}",
            "group by DATE_FORMAT(create_time, #{format})",
            "</script>"})
    List<ProductStatDTO> statTotalNumByDay(@Param("stationId") String stationId, @Param("format") String format,
                                           @Param("begin") String begin, @Param("end") String end);

    @Select({"<script>",
            "SELECT DATE_FORMAT(create_time, #{format}) as formattedCreateTime, count(1) as count FROM product WHERE 1 = 1",
            "<when test='stationId!=null &amp; stationId!=\"\"'>",
            "AND station_id = #{stationId}",
            "</when>",
            "AND type=1",
            "AND DATE_FORMAT(create_time, #{format}) between #{begin} and #{end}",
            "group by DATE_FORMAT(create_time, #{format})",
            "order by DATE_FORMAT(create_time, #{format})",
            "LIMIT #{page},#{limit}",
            "</script>"})
    List<ProductStatDTO> statTotalNumByDayPage(@Param("stationId") String stationId, @Param("format") String format,
                                               @Param("begin") String begin, @Param("end") String end,
                                               @Param("page") int page, @Param("limit") int limit);

}
